lyfz_repos/铂金版源码/DBFile/SQL/2015-08-13[收集]/2015-08-15DB.txt

if   exists(select   1   from   sysobjects   where   id=object_id('fn_GetOrderArrears')   and   objectproperty(id,'IsInlineFunction')=0)  
BEGIN
	DROP function [dbo].fn_GetOrderArrears
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetOrderArrears]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'--欠款
CREATE function [dbo].[fn_GetOrderArrears](@FID varchar(800))
/****** 
分类字符转换
创建人：滕工
创建日期：2014-6-23 14:55:29 
修改人：
修改说明：
修改日期：
******/
Returns varchar(800)
As
Begin
	Declare @Arrears decimal(10, 2)
	Declare @Aoumnt decimal(10, 2)

	set @Arrears = (select Ord_SeriesPrice from tb_ErpOrder where Ord_Number = @FID)
	set @Aoumnt = (Select sum(Plu_Amount) as Plu_Amount from tb_ErpPlusPickItems where Plu_OrdNumber = @FID)
	if @Aoumnt is not null
	begin
		set @Arrears = @Arrears + @Aoumnt		
	end
		set @Aoumnt = 0
	set @Aoumnt = (select sum(Pay_AmountOf) from dbo.tb_ErpPayment where Pay_OrdNumber =@FID)
	if @Aoumnt is not null
	begin
		set @Arrears = @Arrears - @Aoumnt	
	end
	Return  @Arrears
End		
' 
END


if   exists(select   1   from   sysobjects   where   id=object_id('fn_GetOrderReceivable')   and   objectproperty(id,'IsInlineFunction')=0)  
BEGIN
	DROP function [dbo].fn_GetOrderReceivable
END
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fn_GetOrderReceivable]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'
--应收帐款
CREATE function [dbo].[fn_GetOrderReceivable](@FID varchar(800))
/****** 
分类字符转换
创建人：滕工
创建日期：2014-6-23 14:55:29 
修改人：
修改说明：
修改日期：
******/
Returns varchar(800)
As
Begin
	Declare @Arrears decimal(10, 2)
	Declare @Aoumnt decimal(10, 2)

	set @Arrears = (select Ord_SeriesPrice from tb_ErpOrder where Ord_Number = @FID)
	set @Aoumnt = (Select sum(Plu_Amount) as Plu_Amount from tb_ErpPlusPickItems where Plu_OrdNumber = @FID)
	if @Aoumnt is not null
	begin
		set @Arrears = @Arrears + @Aoumnt		
	end	
	Return  @Arrears
End	
' 
END
GO


IF EXISTS (SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE TABLE_NAME = N'Vwp_GetLastClientTrackRecord')
BEGIN
	DROP VIEW [dbo].Vwp_GetLastClientTrackRecord
END 
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[Vwp_GetLastClientTrackRecord]'))
EXEC dbo.sp_executesql @statement = N'
CREATE VIEW [dbo].[Vwp_GetLastClientTrackRecord]
AS
SELECT   id, TR_CustomerGroupID, TR_CustomerID, TR_TraceWay, TR_TraceType, TR_SpecificMatters, 
TR_Communicationstatus, TR_TraceDetailedly, TR_CallRecording, TR_Remark, TR_TraceDateTime, 
TR_TraceTimeLength, TR_TracePersonID, TR_CreateDateTime, TR_UpdateDateName
FROM      dbo.tb_ErpCustomersTrackRecord
WHERE   (id IN
(SELECT   MAX(id) AS Expr1
FROM      dbo.tb_ErpCustomersTrackRecord AS tb_ErpCustomersTrackRecord_1
GROUP BY TR_CustomerGroupID,TR_TraceType))
' 
GO